import sqlite3
import os

OLD_DB_FILE = 'test copy.db'
NEW_DB_FILE = 'train.db'

def migrate_enroll_data():
    if not os.path.exists(OLD_DB_FILE):
        print(f"错误：源数据库文件 '{OLD_DB_FILE}' 不存在。")
        return

    try:
        # 连接旧数据库
        old_conn = sqlite3.connect(OLD_DB_FILE)
        old_conn.row_factory = sqlite3.Row # 允许通过列名访问
        old_cursor = old_conn.cursor()

        # 连接新数据库
        new_conn = sqlite3.connect(NEW_DB_FILE)
        new_cursor = new_conn.cursor()

        # 查询旧数据库中的enroll表数据
        old_cursor.execute("SELECT * FROM enroll")
        rows = old_cursor.fetchall()

        print(f"从 '{OLD_DB_FILE}' 的 'enroll' 表中读取到 {len(rows)} 条记录。")

        # 准备插入新数据库的语句
        # 注意：这里假设 train.db 的 enroll 表已经包含所有新字段
        # 并且我们只迁移旧表已有的字段，并根据 paied 字段设置 status
        insert_sql = """
        INSERT INTO enroll (
            name, company, position, phone, email, order_id,
            training_class_name, openid, price, paied, status,
            created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """

        migrated_count = 0
        for row in rows:
            # 映射 paied 到 status
            status = 'paid' if row['paied'] else 'unpaid'

            # 准备要插入的数据
            data_to_insert = (
                row['name'],
                row['company'],
                row['position'],
                row['phone'],
                row['email'],
                row['order_id'],
                row['training_class_name'],
                row['openid'],
                row['price'],
                row['paied'], # 保留 paied 字段，因为 train.db 仍然有它
                status,
                row['created_at'],
                row['updated_at']
            )
            try:
                new_cursor.execute(insert_sql, data_to_insert)
                migrated_count += 1
            except sqlite3.IntegrityError as e:
                # 如果 order_id 是 UNIQUE 且已存在，会抛出此错误
                print(f"警告：插入记录失败，可能 order_id '{row['order_id']}' 已存在于 '{NEW_DB_FILE}' 中。错误：{e}")
            except Exception as e:
                print(f"插入记录时发生未知错误：{e}")

        new_conn.commit()
        print(f"成功迁移 {migrated_count} 条记录到 '{NEW_DB_FILE}' 的 'enroll' 表。")

    except sqlite3.Error as e:
        print(f"数据库操作错误: {e}")
    finally:
        if 'old_conn' in locals() and old_conn:
            old_conn.close()
        if 'new_conn' in locals() and new_conn:
            new_conn.close()

if __name__ == "__main__":
    migrate_enroll_data()
